package ibmetp.mis.dao;

import ibmetp.mis.vo.Dept;
import ibmetp.mis.vo.Page;
import ibmetp.mis.vo.Resource;
import ibmetp.mis.vo.User;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 用户数据库操作类
  * @author LAN DABIN
 * @created 2011-9-30
 *
 */
public class UserDao extends BaseDao {
	/**
	 * 按Id查询
	 * 
	 * @param id
	 * @return 返回用户信息
	 */
	public User getUserById(Long id) {
		String sql = "select u.*,d.dept_name,d.dept_id from BAS_T_USER u,BAS_T_DEPT d where  u.user_id=? and u.dept_id=d.dept_id(+) ";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;
		User user =null;// 
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();

			while (rs != null && rs.next()) {
				user=new User();
				user.setUserId(rs.getLong("USER_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				user.setUserNo(rs.getString("USER_NO"));
				user.setEmail(rs.getString("EMAIL"));
				user.setDescript(rs.getString("DESCRIPT"));			 
			    System.out.println(user);
				if(rs.getLong("DEPT_ID")!=0)
				{   
					Dept dept=new Dept();
					dept.setDeptId(rs.getLong("DEPT_ID"));
					dept.setDeptName(rs.getString("DEPT_NAME"));
					user.setDept(dept);
					
				}
				

			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;

	}
	/**
	 * 选择用户进入部门
	 * @param userIds
	 * @param deptId
	 * @return
	 */
    public String intoDept(String userIds,Long deptId)
    {
         String sql="update BAS_T_USER set DEPT_ID=?,UPDATE_DATE=sysdate where USER_ID IN ("+userIds+")";
         
		
		String result=null;
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			
			ps = conn.prepareStatement(sql);
			ps.setLong(1,deptId);
			ps.execute();			
			ps.close();
			conn.close();
		} catch (SQLException e) {
			result=e.getMessage();
			e.printStackTrace();
		}
		return result;
    }
	/**
	 * 设置用户ID为userId用户deptId为NULL
	 * @param userId用户ID
	 */
	public void outOfDept(String userIds){
		String sql="update BAS_T_USER set DEPT_ID=NULL,UPDATE_DATE=sysdate where USER_ID IN ("+userIds+")";
			
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			
			ps = conn.prepareStatement(sql);
			//ps.s(6,userId);
			ps.execute();			
			ps.close();
			conn.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	}
	
	/**
	 * 通过用户Id更新用户信息
	 * @param user
	 */
	public void updateUser(User user)
    {
    	String sql="update BAS_T_USER set USER_NAME=?,EMAIL=?,USER_NO=?,DESCRIPT=?,UPDATE_DATE=sysdate where USER_ID=?";
    	if(user.getPwd()!=null&&user.getPwd().trim()!="")
    	{
    		sql="update BAS_T_USER set USER_NAME=?,EMAIL=?,USER_NO=?,DESCRIPT=?,PWD=? where USER_ID=?";
    	}
    	System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2,user.getEmail());
			ps.setString(3,user.getUserNo());
			ps.setString(4,user.getDescript());
			
			
			if(user.getPwd()!=null&&user.getPwd().trim()!="")
	    	{
	    		ps.setString(5,user.getPwd());
	    		ps.setLong(6,user.getUserId());
	    		
	    	}
			else ps.setLong(5,user.getUserId());
			ps.execute();
			//ResultSet rs = ps.executeQuery();
			
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    } 
	
	/**
	 * 批量添加用户
	 * @param users
	 */
	public void addUsers(List<User> users)
	{
        String sql="insert into BAS_T_USER (USER_ID,USER_NAME,EMAIL,USER_NO,DESCRIPT,CREATE_DATE)values (USER_SEQ.NEXTVAL,?,?,?,?,sysdate) ";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			ps = conn.prepareStatement(sql);
			for(int i=0;i<users.size();i++){
			User user=users.get(i);
			ps.setString(1, user.getUserName());
			ps.setString(2,user.getEmail());
			ps.setString(3,user.getUserNo());
			ps.setString(4,user.getDescript());
			//ps.setLong(5,user.getDept().getDeptId());
			ResultSet rs = ps.executeQuery();
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 添加用户
	 * @param user
	 */
	public void addUser(User user) {
		String sql="insert into BAS_T_USER (USER_ID,USER_NAME,EMAIL,USER_NO,DESCRIPT)values (USER_SEQ.NEXTVAL,?,?,?,?) ";
		
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2,user.getEmail());
			ps.setString(3,user.getUserNo());
			ps.setString(4,user.getDescript());
			//ps.setLong(5,user.getDept().getDeptId());
			ResultSet rs = ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 根据用户Id删除用户信息
	 * 
	 * @param id
	 */
	public void deleteById(Long id) {

		String sql = "delete from BAS_T_USER t where t.USER_ID=?";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	
	/**
	 * 批量删除用户
	 * @param userIds
	 */
	public void deleteByIds(String userIds)
	{
		String sql = " delete from BAS_T_USER t where t.USER_ID IN ("+userIds+")";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;

		try {
			ps = conn.prepareStatement(sql);
			//ps.setString(1, userIds);
			ResultSet rs = ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
   
	/**
	 * 查询用户信息
	 * @param pageNo 当前页面
	 * @param pageSize页面大小
	 * @param user 用户条件
	 * @return
	 */
	public Page getUserPage(int pageNo, int pageSize, Object user) {

		return this.find(user, pageNo, pageSize, "(select u.*,d.dept_name from BAS_T_USER u,BAS_T_DEPT d where u.dept_id=d.dept_id(+))");
	}
	
	/**
	 * 搜索还未分配部门的用户
	 * @param pageNo 当前页面
	 * @param pageSize页面大小
	 * @param user 用户条件
	 * @return
	 */
    public Page getFreeUsers(int pageNo,int pageSize,Object user)
    {
    	return this.find(user, pageNo, pageSize, "(SELECT * FROM BAS_T_USER WHERE DEPT_ID IS NULL)");
    }
    
	/**
	 * 将数据集合转换为实体类列表
	 */
	public List ResultSet2List(ResultSet rs) {
		List<User> resultList = new ArrayList<User>();
		try {
			while (rs != null && rs.next()) {
				User user = new User();
				user.setUserId(rs.getLong("USER_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				user.setUserNo(rs.getString("USER_NO"));
				user.setEmail(rs.getString("EMAIL"));
				user.setDescript(rs.getString("DESCRIPT"));
				if(rs.getLong("DEPT_ID")!=0)
				{   
					Dept dept=new Dept();
					dept.setDeptId(rs.getLong("DEPT_ID"));					
					dept.setDeptName(rs.getString("DEPT_NAME"));
					user.setDept(dept);
					
				}

				resultList.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultList;
	}
	
	/**
	 * 根据用户编号获取用户信息
	 * @param userNo
	 * @return
	 */
	public User getUserByNo(String userNo)
	{
		String sql = "select * from BAS_T_USER t where t.USER_NO=?";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;
		User user =null;// 
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userNo);
			ResultSet rs = ps.executeQuery();

			while (rs != null && rs.next()) {
				user=new User();
				user.setUserId(rs.getLong("USER_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				user.setUserNo(rs.getString("USER_NO"));
				user.setEmail(rs.getString("EMAIL"));
				user.setDescript(rs.getString("DESCRIPT"));
				

			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}
	
	/**
	 * 添加查询条件
	 */
	public Map<Integer, Object[]> getCondition(Object o) {
		Map<Integer, Object[]> paramsMap = new HashMap<Integer, Object[]>();
		Integer count = 0;
		User user = (User) o;
		if (user.getUserName() != null
				&& user.getUserName().trim().length() > 0) {
			count++;
			paramsMap.put(count, new Object[] { " and USER_NAME like ? ",
					"%" + user.getUserName() + "%" });
		}
		if (user.getUserNo() != null
				&& user.getUserNo().trim().length() > 0) {
			count++;
			paramsMap.put(count, new Object[] { " and USER_NO like ? ",
					"%" + user.getUserNo() + "%" });
		}
		if(user.getDept()!=null)
		{
			if(user.getDept().getDeptId()!=null)
			{
				count++;
				paramsMap.put(count, new Object[] { " and DEPT_Id=? ",
						 user.getDept().getDeptId()});
			}
		}
		return paramsMap;
	}
	/**
	 * 获得某个角色下的用户列表
	 * @param roleId 角色Id
	 * @param user   用户查询条件
	 * @param pageNo 页面号码
	 * @param pageSize 页面大小
	 * @return
	 */
	public Page getRoleUsers(int pageNo,int pageSize,Long roleId,Object user)
	{
		
		return this.find(user, pageNo, pageSize, "(SELECT t.*,d.DEPT_NAME FROM BAS_T_USER t,BAS_T_DEPT d  WHERE EXISTS(SELECT * FROM BAS_T_USER_ROLE ur where ur.user_id=t.user_id and ur.role_id="+roleId+") and t.DEPT_ID=d.DEPT_ID(+))");
		
	}
	
	/**
	 * 根据用户编号和密码判断用户是否存在
	 * @param userNo
	 * @param pwd
	 * @return
	 */
	public User hasUser(String userNo,String pwd)
	{
		String sql = "select u.*,d.DEPT_NAME from BAS_T_USER u,BAS_T_DEPT  d where u.USER_NO=? and u.PWD=? and u.DEPT_ID=d.DEPT_ID(+)";
		System.out.println(sql);
		conn = DBUtil.getConnection();
		PreparedStatement ps;
		User user =null;// 
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, userNo);
			ps.setString(2,pwd);
			ResultSet rs = ps.executeQuery();

			while (rs != null && rs.next()) {
				user=new User();
				user.setUserId(rs.getLong("USER_ID"));
				user.setUserName(rs.getString("USER_NAME"));
				user.setUserNo(rs.getString("USER_NO"));
				user.setEmail(rs.getString("EMAIL"));
				user.setDescript(rs.getString("DESCRIPT"));
				if(rs.getLong("DEPT_ID")!=0)
				{   
					Dept dept=new Dept();
					dept.setDeptId(rs.getLong("DEPT_ID"));
					dept.setDeptName(rs.getString("DEPT_NAME"));
					user.setDept(dept);
					
				}
				

			}
			 List<Resource> resources=new ArrayList();
			//获取用户资源
			if(user!=null)
			{
              sql="Select * From Bas_T_Resource Re Where Exists (Select * From Bas_T_Role_Resource Rr Where Exists (select * from bas_t_user_role ur where ur.user_id=? and ur.role_id=rr.role_Id )  And Rr.Resource_Id=Re.Resource_Id)";
			  System.out.println(sql);
              ps=conn.prepareStatement(sql);
			  ps.setLong(1,user.getUserId());
			  rs = ps.executeQuery();
			  while (rs != null && rs.next()) {
					Resource r=new Resource();
					//System.out.println(rs.getString("RESOURCE_NAME"));
					r.setResourceId(rs.getLong("RESOURCE_ID"));
					r.setResourceName(rs.getString("RESOURCE_NAME"));
					r.setParentId(rs.getLong("PARENT_ID"));
					//System.out.println(rs.getString("RESOURCE_NAME")+rs.getLong("PARENT_ID"));
					r.setUrl(rs.getString("URL"));
					r.setDescript(rs.getString("DESCRIPT"));
					resources.add(r);
				}
			  user.setResources(resources);
			}
			
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}

}
